NSQL Functions - A to Z

ABS

Removes the sign from each record in the column.

ACOS

Returns the ArcCos of the column/value in Radians

AGE

Returns the number of years between two dates.

AGE

Returns the number of years between two dates.

Remarks

Both [DATEFIELD] parameters must be of the same data type, either DATE_FIELD or DATETIME_FIELD. Functions DATE and DATETIME can be used for data type conversion.

Examples

ALLMONTHS

Returns the year and month parts of a date field, in the format CCYYMM (e.g. 199805 would be returned where the specified datefield contained a date which fell in May 1998).

ALLQUARTERS

Returns the year and quarter of a date field, in the format CCYYQQ (e.g. 199802 would be returned where the specified datefield contained a date which fell in the second quarter of 1998).

[RESULT] = ALLQUARTERS([DATEFIELD])

ASIN

Returns the ArcSin of the column/value in Radians

[RESULT] = ASin([COLUMN])

ATAN

Returns the ArcTan of the column/value in Radians

[RESULT] = ATAN([COLUMN])

AVG

Returns the average value of a set of values.

[RESULT] = Avg([FIELDNAME])

Examples

To return the average of the values in a column:

SELECT AVG([FIELD]) FROM [TABLE];

BIGINT

Returns the integer or whole number part of the input data, and returns the value as a BIGINT column. The number will be rounded to a whole number, eg: 1.7 will be returned as 2. Used in an expression to force the creation of a BIGINT_FIELD.

[RESULT] = BIGINT([VALUE])

CEIL

Returns the smallest integer greater than or equal to a value.

[RESULT] = CEIL([NUMERICFIELD])

Example

SELECT CEIL([Demo].[Order Detail].[Sale Price]) FROM [Demo].[Order Detail];

Returns 6 when Sale Price = 5.10.

Returns 6 when Sale Price = 5.50.

Returns 6 when Sale Price = 5.99.

See also

FLOOR function

CHR

Returns the ASCII character represented by a numeric column (mod 256).

[RESULT] = CHR([NUMERICFIELD])

CODE

Returns the ASCII code of the first character.

[RESULT] = CODE([FIELDNAME])

CONTAINSALL

Returns all records where ALL of the tokens specified are present.

[RESULT] = ContainsAll([FIELDNAME]) [OPERATOR] [VALUE]

Example

The following query will return ALL records where the NON-DECODED field Question1 contains token 1 and 2 (including those where other values are also present):

The following query will return ALL records where the NON-DECODED field Question1 contains token 1 and 2 (including those where other values are also present):

The following query will return all records where the DECODED field Question1 contains the tokens "UK" and "FRANCE" (including those where other values are also present):

SELECT # FROM [Demo].[Customer] WHERE ContainsAll(Question1) IN "UK", "FRANCE";

Note the following is not a valid query:

SELECT # FROM [Demo].[Customer] WHERE ContainsAll(Question1) = "UK,FRANCE";

CONTAINSEXACTLY

Returns all records where the tokens contained match only those specified.

[RESULT] = ContainsExactly([FIELDNAME]) [OPERATOR] [VALUE]

Example

To return records where the NON-DECODED field Question1 contains only the tokens 1 and 2 (in any order - i.e. 12 or 21):

SELECT # FROM [Demo].[Customer] WHERE ContainsExactly(Question1) = "12"; SELECT # FROM [Demo].[Customer] WHERE ContainsExactly(Question1) = "12";

The following query will return all records where the DECODED field Question1 contains only the token "UK":

SELECT # FROM [Demo].[Customer] WHERE ContainsExactly(Question1) = "UK";

The following query will return all records where the DECODED field Question1 contains BOTH "UK" and "FRANCE":

SELECT # FROM [Demo].[Customer] WHERE ContainsExactly(Question1) IN "UK", "FRANCE";

COS

Returns the Cos of the column/value in Radians

[RESULT] = Cos([COLUMN])

COUNT

Returns the number of records or values in a set of values.

[RESULT] = Count([VALUE]) [EXPRESSION]

Remarks

It is not possible to list more than one FIELD_NAME within the COUNT statement:

SELECT COUNT ([FIELD_NAME1], [FIELD_NAME2]) FROM [TABLE];

COUNT does not support embedded functions, for example:

SELECT COUNT (DISTINCT [FIELD_NAME2]) FROM [TABLE];

Example

To return the count of all rows in the table:

SELECT COUNT (*) FROM [TABLE];

COUNTTOKENS

Returns all records where the number of tokens matches the specified value.

[RESULT] = CountTokens([FIELDNAME]) [OPERATOR] [VALUE]

Example

To return all records where the customer specified 3 or more answers for question 1:

SELECT # FROM [Demo].[Customer] WHERE CountTokens(Question1) => 3;

CUMUL

Returns the cumulative value of all column records or the running balance.

[RESULT] = CUMUL([FIELDNAME])

DATE

Returns the input data converted from formatted text to a DATE data type value. Used in an expression to force the creation of a DATE_FIELD.

[RESULT] = DATE( [VALUE] , [FORMAT])

Remarks

Note that regardless of the INPUT format (as specified by [FORMAT]) the data will be stored in the format specified by SystemDateFormat. It is this format that must be used when evaluating the DATE in expressions or queries.

Examples

If DOB is a string column in the format "CCYYMMDD" then:

SELECT DATE([Demo].[Person].[DOB]) FROM [Demo].[Person];

returns 13-08-2002 when DOB = 20021308.

DATETIME

Returns a DATETIME_FIELD representation of the input data. Used in an expression to force the creation of a DATETIME_FIELD. Supports 2 types of syntax:

Syntax1 [RESULT] = DATETIME([VALUE1])

Syntax2 [RESULT] = DATETIME([VALUE1], [VALUE2])

Remarks

If no Date value is specified, or if the Date Value is NULL, then a base year of 1900 is used. If the TIME value is NULL or not specified then Midnight (00:00:00) will be used.

Examples

The following returns 10/12/1955 23:32:00 where :

• DATETIMESTRING = 19551012 2332 and
• SystemDateTimeFormat is "CCYYMMDD HHMM"

SELECT DATETIME([Demo].[Person].[DATETIMESTRING]) FROM [Demo].[Person];

The following returns 10/12/1955 23:32:14 where:

• DATEFIELD1 = 10121955
• TIMEFIELD1 = 233214
• SystemDateFormat is "CCYYMMDD" and
• SystemTimeFormat is "HHMMSS"

SELECT DATETIME([Demo].[Person].[DATEFIELD1],[Demo].[Person].[TIMEFIELD1] ) FROM [Demo].[Person];

DAY

Returns the day portion of each record (1-31).

[RESULT] = DAY([DATECOLUMN])

Examples

If DOB is a date column in the format "DDMMCCYY" then:

SELECT DAY([Demo].[Person].[DOB]) FROM [Demo].[Person];

returns 13 when DOB = 13082006.

DAYAT

Adds or subtracts (with minus sign) [integer] number of days to/from a date field.

[RESULT] = DAYAT([DATEFIELD], integer)

Examples

If DOB is a date column in the format "DDMMCCYY" then:

SELECT DAYAT([Demo].[Person].[DOB], 30) FROM [Demo].[Person];

returns 22022006 when DOB = 23012006.

DAYSTO

Returns the number of days between 2 dates.

[RESULT] = DAYSTO([PARAM1], [PARAM2])

Remarks

Both parameters must be of the same data type, either DATE_FIELD or DATETIME_FIELD. Functions DATE and DATETIME can be used for data type conversion.

Examples

SELECT * FROM [TABLE] WHERE DAYSTO([FIELD1],[FIELD2])< 200;

DISTANCE

Calculates the distance between two geographic points.

[RESULT] = DISTANCE([FIELDNAME], [Value])

Remarks

Requires Mapping configuration settings in Cerberus.Ini.

Example

SELECT Count(*) FROM [Customers] WHERE DISTANCE([Postcode],'BS2 9AG') < 1609;

DISTANCEBETWEEN

Returns the Great Circle Distance between two Latitude, Longitude points.

Part Description [RESULT] Data Type = REAL_FIELD [Lat1] Data Type = REAL_FIELD [Long1] Data Type = REAL_FIELD [Lat2] Data Type = REAL_FIELD [Long2] Data Type = REAL_FIELD Unit Fixed integer

Returns the Great Circle Distance between two Latitude, Longitude points.

[RESULT] = DISTANCEBETWEEN("[Lat1],[Long1], [Lat2],[Long2],Unit")

Unit may be:

0: Nautical Miles

1: Meters

2: Kilometers

3: Statute Miles

4: Feet

5: Yards

6: Radians

Note that the parameters are specified as a single quoted string rather than as separate parameters.

DIGEST

Returns the 128bit MD5 hash of one or more columns.

[RESULT] = DIGEST(“[COLUMN_1], [COLUMN_2],…, [COLUMN_N])

Remarks

Digest can be used to create a compound key of multiple columns without having to resort to column concatenation.

The probability of a hash collision is very low, but cannot be absolutely guaranteed.

Note that the Digest expression can take any reasonable number of columns as input but these are formed as a single quoted string rather than as separate parameters.

Example

EXPRESSION [Demo].[Customer] , MyKey , {DIGEST("[Cust ID],[Household ID],Age")}

DISTINCT

Returns the record position of each value in a column's sorted list of distinct values.

[RESULT] = DISTINCT([FIELDNAME])

Example

For a column containing the following records:

SELECT EXPR{ DISTINCT([Town]) }, [Town] FROM [Demo].[Customer];

returns:

since in a sorted, distinct list, Bath is in position 1, Birmingham in position 2, and Bristol in position 3.

EODAY

Returns the end of the day of a date or datetime value, plus or minus a day offset.

[RESULT] = EODAY([COLUMN],dayOffset)

Example

End of current day

EXPRESSION [Demo].[Customer] , DayEnd , {EODAY(DOB,0)}

End of previous day

EXPRESSION [Demo].[Customer] , YesterdayEnd , {EOMONTH(DOB,-1)}

EOMONTH

Returns the end of the month of a date or datetime value, plus or minus a month offset.

[RESULT] = EOMONTH([COLUMN],monthOffset)

Example

End of current month:

EXPRESSION [Demo].[Customer] , MonthEnd , {EOMONTH(DOB,0)}

End of Previous month:

EXPRESSION [Demo].[Customer] , PrevMonthEnd , {EOMONTH(DOB,-1)}

EXP

Returns the exponential of each column record. The column must hold numeric values.

Returns the exponential of each column record. The column must hold numeric values.

FIRSTQUARTILE

Returns the value at the first quartile of the column.

[RESULT] = FIRSTQUARTILE([NUMERIC_COLUMN])

Example

For a column containing 20 records with values 1 to 20, the following NSQL would return 5.00:

SELECT FIRSTQUARTILE([COLUMN]) FROM [DB].[TABLE];

FLOOR

Returns the largest integer less than or equal to each record.

[RESULT] = FLOOR([COLUMN])

Example

SELECT FLOOR([Demo].[Order Detail].[Sale Price]) FROM [Demo].[Order Detail];

Returns 5 when Sale Price = 5.10.

Returns 5 when Sale Price = 5.50.

Returns 5 when Sale Price = 5.99

See also

CEIL function

HOUR

Returns the hours part of a datetime or time column, in 24-hour format.

[RESULT] = HOUR([DATETIME_COLUMN])

Example

Given a table of bank transactions, the following would return all transactions that took place between 9 am and 3 pm:

SELECT Count(*) FROM [Transaction] WHERE HOUR([Transaction_Time]) BETWEEN 9 and 15;

INSTR

[RESULT] = INSTR([COLUMN], [STRING])

Remarks

The position of the first character of [COLUMN] is 1. If [STRING] is not found, 0 is returned.

The search is case sensitive.

Example

If you were to search for the word "East" in a column holding the names of regions in the UK:

SELECT INSTR([Demo].[Household].[Regions], "East") FROM [Demo].[Household];

returns 0 when [Demo].[Household].[Regions] is Channel Islands.

returns 1 when [Demo].[Household].[Regions] is East Midlands.

returns 7 when [Demo].[Household].[Regions] is South East.

INT

Returns the integer or whole number part of the input data. The number will be rounded to a whole number, eg: 1.7 will be returned as 2. Used in an expression to force the creation of an INTEGER_FIELD.

[RESULT] = INT([VALUE])

Remarks

Numbers are rounded to the nearest whole numbers. For example, 1.7 will be rounded up to 2, 1.3 will be rounded down to 1.0 and 1.5 will be rounded up to 2.

If a column contains a value that cannot be converted into an integer, the value will be stored as NULL or 0.

Examples

The following returns 1001 when code = "1001":

SELECT INT([Demo].[Person].[code]) FROM [Demo].[Person];

ISNULL

Returns 0 where the column holds null, and 1 where the value is not null.

[RESULT] = ISNULL([COLUMN])

Example

The following returns a count of customers where the Surname field contains null:

SELECT Count(*) FROM [Demo].[Customers] WHERE ISNULL([Demo].[Customers].[Surname]) = 1;

IURN

Returns the Internal Engine URN for a record.

[RESULT] = IURN([VALUE]) [EXPRESSION]

Remarks

Every record in every table has an internal URN. Engine URNs are 0 based.

When used in the WHERE clause of a query, must use EXPR{} syntax.

Examples

To return the Internal URN of all rows in the table:

SELECT IURN(FID) FROM [TABLE];

To delete the first 20 rows of a table:

DELETE * FROM [TABLE] WHERE EXPR{IURN([DB].[TABLE].[FID])} BETWEEN 0 and 19;

KURTOSIS

Returns a value indicating the kurtosis of a column.

[RESULT] = KURTOSIS([COLUMN])

LCASE

Returns the values of a text column in all lower case.

[RESULT] = LCASE([COLUMN])

Examples

The following query:

SELECT LCASE([Demo].[Customer].[Surname]) FROM [Demo].[Customer];

returns smith when Surname = Smith

returns smith when Surname = SMITH

returns smith when Surname = smith

LEFT

Returns a specified number of characters from the left of a text column.

[RESULT] = LEFT([COLUMN], [NUMBER])

LEN

Returns the length of each record in the column.

[RESULT] = LEN([COLUMN])

LOG

Returns the natural logarithm of the column records.

[RESULT] = LOG([COLUMN])

LOG10

Returns the base 10 logarithm of the column records.

[RESULT] = LOG10([COLUMN])

LTRIM

Returns the values of a text column after removing any spaces to the left of the column.

[RESULT] = LTRIM([COLUMN])

MAX

Returns the maximum value of a set of values.

[RESULT] = MAX([FIELDNAME])

Examples

To return the maximum value from all values in a column:

SELECT MAX([FIELD) FROM [TABLE];

MAXIMUM

Returns whichever is the greater of two values.

[RESULT] = MAXIMUM([COLUMN], [COLUMN])

MEAN

Returns the average value of a set of values.

[RESULT] = Mean([FIELDNAME])

Examples

To return the average of all values in a column:

SELECT MEAN([NUMERICFIELD) FROM [TABLE];

MEDIAN

Returns the middle value of the column.

[RESULT] = MEDIAN([COLUMN])

MID

Returns a substring from each record of the column.

[RESULT] = MID([COLUMN], [NUMBER], [NUMBER])

MIN

Returns the minimum value of a set of values.

[RESULT] = Min([FIELDNAME])

Examples

To return the minimum value from all values in a column:

SELECT MIN([FIELD) FROM [TABLE];

MINIMUM

Returns whichever is the lower of two values.

[RESULT] = MINIMUM([COLUMN], [COLUMN])

MINUTE

[RESULT] = MINUTE([DATETIME_COLUMN])

MODE

Returns the value that occurs most often in a column.

[RESULT] = MODE([COLUMN])

MONTH

Returns the month portion of each record (1-12).

[RESULT] = MONTH([DATE_COLUMN])

NORMALISED

Returns the normalized value of each record in the column, defined as the difference between each value and the average value, divided by the standard deviation.

[RESULT] = NORMALISED([COLUMN])

PAREA

Returns the Postal Area from a UK-Style postcode. The postal area is the first 2 letters, or the first letter if the 2nd character is a number. Parameter can be a string or a column containing postcodes.

[RESULT] = PAREA([PARAM])

Examples

SELECT PAREA([Demo].[Person].[Postcode]) FROM [Demo].[Person]

Returns "CF" for "CF64 1AU" and "B" for "B1 8NN"

PDIST

Returns the Postal District from a UK-Style postcode. The Postal District is the postcode minus the last three chars, with all spaces removed. Parameter can be a string or a column containing postcodes.

[RESULT] = PDIST([PARAM])

Examples

SELECT PDIST([Demo].[Person].[Postcode]) FROM [Demo].[Person]

Returns "PO1" for "PO1 3AX".

POWER

Returns each column record raised to the power of [number]

[RESULT] = POWER([COLUMN], [NUMBER])

PROPER

Returns a column's data in initial capitals (ie lower case with a leading capital).

[RESULT] = PROPER([COLUMN])

PROPERSENTENCE

Returns a column's data in lower case but with a leading capital on every new word.

For example "the quick brown fox" would become "The Quick Brown Fox"

[RESULT] = PROPERSENTENCE([COLUMN])

PSECT

Returns the Postal Sector from a UK-Style postcode. The Postal Sector is the postcode minus the last two chars, with all spaces removed. The string [PARAM] is a string constant or the name of a column containing UK postcodes.

[RESULT] = PSECT([PARAM])

Examples

SELECT PDIST([Demo].[Person].[Postcode]) FROM [Demo].[Person]

Returns "PO13" for "PO1 3AX".

QUARTER

Returns the quarter of the year for each record (1-4).

[RESULT] = QUARTER([DATE_COLUMN])

RAND

Returns a random number between 0 and the value of each record in the column.

[RESULT] = RAND([COLUMN])

REAL

Returns the integer or whole number part of the input data. Used in an expression to force the creation of a REAL_FIELD.

[RESULT] = REAL( [VALUE], [PRECISION] )

Remarks

If [PRECISION] is specified, then the function must be called within the EXPR{} syntax. Otherwise it can be called directly.

Examples

The following returns 10.00 when Income = "10" and DefaultPrecision = 2:

SELECT REAL([Demo].[Person].[Income]) FROM [Demo].[Person];

The following returns 10.000 when Income = 10:

SELECT EXPR{REAL([Demo].[Person].[Income],3) } FROM [Demo].[Person];

RIGHT

Returns [number] characters from the right of the column.

[RESULT] = RIGHT([COLUMN], [NUMBER])

RMEAN

Returns the average value of a set of values as a Real.

[RESULT] = RMean( [FIELDNAME] )

Examples

To return the average of all values in a column as a Real:

SELECT EXPR{RMEAN([NUMERICFIELD)} FROM [TABLE];

ROW

As for IURN.

[RESULT] = ROW([COLUMN])

RTRIM

Returns the values of a text column after removing any spaces to the right of the column.

[RESULT] = RTRIM([COLUMN])

SECOND

Returns the seconds part of a datetime or time column.

[RESULT] = SECOND([DATETIME_COLUMN])

SGN

Returns the sign of each record, -1 for negative numbers, +1 for positive numbers or 0.

[RESULT] = SGN([COLUMN])

SIN

Returns the Sin of the column/value in Radians

[RESULT] = Sin([COLUMN])

SKEW

A measure of the skew of a column.

[RESULT] = SKEW([COLUMN])

SODAY

Returns the start of the day of a date or datetime value, plus or minus a day offset.

[RESULT] = SODAY([COLUMN],dayOffset)

Example

Start of current day

EXPRESSION [Demo].[Customer] , DayStart , {SODAY(DOB,0)}

End of previous day

EXPRESSION [Demo].[Customer] , YesterdayStart , {SODAY(DOB,-1)}

SOMONTH

Returns the start of the month of a date or datetime value, plus or minus a month offset.

[RESULT] = SOMONTH([COLUMN],monthOffset)

Example

Start of current month

EXPRESSION [Demo].[Customer] , MonthStart , {SOMONTH(DOB,0)}

End of previous month

EXPRESSION [Demo].[Customer] , PrevMonthStart , {SOMONTH(DOB,-1)}

SORTFORMAT

Converts a postcode to Sort Format in a column containing UK postcodes.

[RESULT] = SORTFORMAT([COLUMN])

SOUNDS

Returns the values of the column after removing all vowels (and Y) and spaces from the values.

[RESULT] = SOUNDS([COLUMN])

SQRT

Returns the square root of each record.

[RESULT] = SQRT([COLUMN])

STDEV

Returns the population standard deviation, i.e. the average amount by which a set of values deviates on either side of the mean.

[RESULT] = StDev([FIELDNAME])

Examples

To return the standard deviation of the values in a column:

SELECT STDEV([FIELD) FROM [TABLE];

STDEVS

Returns the sample standard deviation of a column.

[RESULT] = STDEVS([COLUMN])

STRING

Returns a string representation of another data type. Used in an expression to force the creation of a TEXT_FIELD.

[RESULT] = String([VALUE])

Remarks

If the input is a Date, a Time or a DateTime the resulting string will be formatted in the user format , as specified in the Cerberus.ini file.

STRING will not convert Unicode data to ASCII format.

Examples

To return all records where [Database].[Table].[Field] = "JONES":

SELECT * FROM [Demo].[Table] WHERE STRING([Database].[Table].[Field])="JONES";

STRIP

Returns the values of the column after removing all spaces.

[RESULT] = STRIP([COLUMN])

SUM

Returns the sum of values in a set of values.

[RESULT] = Sum([FIELDNAME])

Examples

To return the sum of all values in a column:

SELECT SUM([FIELD) FROM [TABLE];

SUMSQ

Returns the sum of squares of a set of values.

[RESULT] = SumSq([FIELDNAME])

Examples

To return the sum of squares of the values in a column:

SELECT SUMSQ([NUMERICFIELD) FROM [TABLE];

TAN

Returns the Tan of the column/value in Radians

[RESULT] = TAN([COLUMN])

THIRDQUARTILE

Returns the value at the third quartile of the column.

[RESULT] = THIRDQUARTILE([COLUMN])

TIME

Returns a TIME_FIELD representation of the input data. Used in an expression to force the creation of a TIME_FIELD.

[RESULT] = TIME( [VALUE], [FORMAT] )

Remarks

If [FORMAT] is not specified, and the input data is not fully qualified (i.e. instead of 01 a 1 is provided), the resulting time may not be correct.

If [FORMAT] is specified, Engine will only evaluate the expression for data that is fully qualified. This prevents incorrect formatting from being applied.

If [FORMAT] is specified, then the function must be called within the EXPR{} syntax. Otherwise it can be called directly.

When using a TIME_FIELD in an expression, note that hours are always stored as 24 hour.

Examples

The following returns 10:12:45 when TIMESTRING = "101245" and SystemTimeFormat is HHMMSS:

SELECT TIME([Demo].[Person].[TIMESTRING]) FROM [Demo].[Person];

The following returns 10:12 when TIMESTRING = "101245":

SELECT EXPR{REAL([Demo].[Person].[TIMESTRING],"HHMM") } FROM [Demo].[Person];

The following returns a count of transactions that occurred between 9am and 3pm:

SELECT COUNT(*) FROM [Demo].[Transaction] WHERE HOUR( [Demo].[Transaction].[TTIME] ) BETWEEN 9 and 15;

The following returns 10:12:45 where DATETIME1 = "19950613 1012":

SELECT TIME([Demo].[Person].[DATETIME1]) FROM [Demo].[Person];

Note that if the SystemDateTimeFormat

is 1 ( CCYYMMDD HHMM), it is still possible to extract HH:MM:SS data from the field.

TRIM

Returns the values of a text column after removing any spaces to the left and right of the column.

[RESULT] = TRIM([COLUMN])

Remarks

TRIM is not an NSQL function but can be used in NSQL with the Expr{} syntax:

SELECT EXPR{ TRIM([Demo].[Customer].[Town]) } FROM [Demo].[Customer];

UCASE

Returns the values of the column converted to upper case.

[RESULT] = UCASE([COLUMN])

WEEKDAY

Returns the day of the week for each record (1-7, with 1 being Monday).

[RESULT] = WEEKDAY([DATE_COLUMN])

WSTRING

Returns a string representation of another data type. Used in an expression to force the creation of a UNICODE_FIELD.

[RESULT] = String([VALUE])

Remarks

If the input is a Date, a Time or a DateTime the resulting string will be formatted in the user format , as specified in the Cerberus.ini file.

WSTRING will convert ASCII input to Unicode format.

Examples

The following will produce a Unicode version of the TOWN field:

SELECT WSTRING([Demo].[Table].[Town]) FROM [Demo].[Table];

YEAR

Returns the year portion of a date.

[RESULT] = YEAR([DATE_COLUMN])

• NSQL Functions Overview
  • NSQL Functions A to Z
  • NSQL Intrinsic Functions
  • DATE Columns
  • Distance Calculations
  • Miscellaneous Functions
  • NUMERIC Columns
  • Text Columns
  • TIME Columns